package blancosMikel.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import blancosMikel.controller.beans.saldosBeans;

public class saldosDAO extends SigaabmDAO {
	public ArrayList<saldosBeans> getApartados(saldosBeans apartado) throws Exception{
		System.out.println("(SaldosDAO.getApartado)...............execute");
		StringBuilder query= new StringBuilder();
		ArrayList<saldosBeans> catalogo = new ArrayList<saldosBeans>();
		try{
			query.append("select * from saldos where id_apartado='")
				 .append(apartado.getId_apartado())
				 .append("'");
			System.out.println("(SaldosDAO.getApartados):------->"+query.toString());
			conn=this.getConnectionMYSQL();
			ps=conn.prepareStatement(query.toString());
			rs=ps.executeQuery();
			if(rs.next()){
				saldosBeans bean = null;
				while (rs.next()) {
				bean = new saldosBeans();
				bean.setId_saldo(rs.getInt(1));
				bean.setFecha(rs.getString(3));
				bean.setAbono(rs.getDouble(4));
				bean.setRestante(rs.getDouble(5));
				System.out.println(catalogo);
				}
				catalogo.add(bean);
			}else{
				throw new Exception("Error aparado no existe.");
			}
		}
		
		catch (SQLException error) {
		throw new Exception(error.getMessage());
	}catch (Exception error) {
		throw new Exception(error.getMessage());
		}
		return catalogo;
	}
	public void InsertarApartado(saldosBeans saldos) {
		try{
			StringBuilder query = new StringBuilder();
        	conn = this.getConnectionMYSQL();
        	query.append("INSERT INTO SALDOS");
        	query.append("(id_saldo,id_apartado,fecha,abono,restante)");
        	query.append("values (");
        	query.append(saldos.getId_saldo());
        	query.append(",");
        	query.append(saldos.getId_apartado());
        	query.append(",");
        	query.append(saldos.getFecha());
        	query.append(",");
        	query.append(saldos.getAbono());
        	query.append(",");
        	query.append(saldos.getRestante());
        	query.append(")");
        	
        	st = conn.createStatement();
            st.executeUpdate(query.toString()); 
            System.out.println(query.toString());
            System.out.println("Agregando nuevo apartado");
        	
		}catch (Exception e) {
            // TODO: handle exception
        }
        close(conn);
        close(st);
	}
	public void BajaApartado(saldosBeans baja) {
		  try {
	        	StringBuilder query = new StringBuilder();
	        	conn = this.getConnectionMYSQL();
	        	
	        	query.append("DELETE FROM saldos where id_apartado=");
	            query.append(baja.getId_apartado());
	            
	            st = conn.createStatement();
	            st.executeUpdate(query.toString()); 
	            System.out.println(query.toString());
	            System.out.println("Registro eliminado satisfactoriamente");
	      
	            
	        } catch (Exception e) {
	            // TODO: handle exception
	        }
	        close(conn);
	        close(st);
		
	}
	@SuppressWarnings("static-access")
    public int getId() {
        int id = 0;
        try {
        	StringBuilder query = new StringBuilder();
            query.append("Select id_saldo from saldos");
            conn = this.getConnectionMYSQL();
            ps = conn.prepareStatement(query.toString());
            rs = ps.executeQuery();
  
            while (rs.next()) {
                if (id < rs.getInt(1)) {
                    id = rs.getInt(1);
                }
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        this.close(conn);
        this.close(ps);
        this.close(rs);
        return id + 1;
    }
	public ArrayList<saldosBeans> getSaldos () throws SQLException {
		ArrayList <saldosBeans>saldosList = new ArrayList<saldosBeans>();
		System.out.println("DAO CARGANDO LISTA DE SALDOS ----->");
		try{
			StringBuilder query = new StringBuilder();
			query.append("Select * from saldos order by id_saldo");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println(query.toString());
		if(rs.next()){
			while(rs.next()){
				saldosBeans saldo = new saldosBeans();
				saldo.setId_saldo(rs.getInt(1));
				saldo.setFecha(rs.getString(3));
				saldo.setAbono(rs.getDouble(4));
				saldo.setRestante(rs.getDouble(5));
				saldosList.add(saldo);
			}
		}else{
			throw new Exception("Error TB.Saldos no tiene registros.");
		}
		}catch (Exception e) {
			System.out.println("SaldosDao...Error..."+e.getMessage());
	}
		close(conn);
		close(ps);
		close(rs);
		System.out.println("Dao -Lista cargada");
		return saldosList;
	}
}
